package com.dean.utils;

import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.dean.constants.DeanUiConstants;
import com.dean.exception.DeanRuntimeException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;

/**
 * dean sql 工具类
 * @author Administrator
 */
public class DeanSqlUtils {
    private static final Logger logger = LoggerFactory.getLogger(DeanSqlUtils.class);

    private static final String ORALCE = "oracle";
    private static final String MYSQL = "mysql";
    private static final String POSTGRESQL = "postgresql";
    private static final String DEAN_SPLIT_SUBSTR="dean_split_substr";
    private static final String DEAN_SUBSTR="dean_substr";

    /**
     * 根据数据库类型 转换表名 大小写
     * @param dbType
     * @param tableName
     * @return
     */
    public static String covertTableName(String dbType,String tableName){
        if(POSTGRESQL.equals(dbType) || MYSQL.equals(dbType)){
            return tableName.toLowerCase();
        }else{
            return tableName.toUpperCase();
        }
    }

    /**
     * 根据 数据库驱动获取数据库类型
     * @param driver
     * @return
     */
    public static String getDbType(String driver){
        String dbType = null;
        if(StrUtil.isBlank(driver)){
            DeanRuntimeException.throwException(102,"数据库驱动不能为空");
        }else if(driver.contains(ORALCE)){
            dbType = ORALCE;
        }else if(driver.contains(MYSQL)){
            dbType = MYSQL;
        }else if(driver.contains(POSTGRESQL)){
            dbType = POSTGRESQL;
        }else{
            DeanRuntimeException.throwException(101,"暂不支持的数据库类型");
        }
        return dbType;
    }

    /**
     * 根据数据类型 获取对应的查询表信息的sql
     * @param dbType
     * @return
     */
    public static String tableInfoSql(String dbType){
        String sql = null;
        switch (dbType){
            case ORALCE:
                sql = "select COMMENTS TABLE_COMMENT,TABLE_NAME from user_tab_comments where table_name = ? and table_type='TABLE'";
                break;
            case MYSQL:
                sql = "SELECT  TABLE_COMMENT TABLE_COMMENT ,TABLE_NAME FROM information_schema.`tables` where table_name = ? and table_type='BASE TABLE'";
                break;
            case POSTGRESQL:
                sql = "select obj_description(b.oid,'pg_class') \"TABLE_COMMENT\" ,a.tablename \"TABLE_NAME\" from pg_tables a, pg_class b where a.tablename = b.relname and a.tablename = ?";
                break;
            default:
                DeanRuntimeException.throwException(1001,"暂不支持的数据库类型") ;
        }
        return sql;

    }

    /**
     * org.postgresql.Driver
     * oracle.jdbc.driver.OracleDriver
     * com.mysql.cj.jdbc.Driver
     * @param dbType
     * @return
     */
    public static String fieldInfoSql(String dbType){
        String sql = null;
        switch (dbType){
            case ORALCE:
                sql = "SELECT A.COLUMN_NAME,A.COMMENTS COLUMN_COMMENT,B.DATA_TYPE,NVL(B.DATA_PRECISION,B.DATA_LENGTH) DATA_LENGTH,B.DATA_SCALE FROM USER_COL_COMMENTS A, USER_TAB_COLS B where A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME=B.COLUMN_NAME AND  A.TABLE_NAME = ? ORDER BY COLUMN_ID";
                break;
            case MYSQL:
                sql = "SELECT COLUMN_NAME,COLUMN_COMMENT,DATA_TYPE,COALESCE(numeric_precision,character_maximum_length) DATA_LENGTH ,numeric_scale as DATA_SCALE from information_schema.columns where table_name = ? order by ordinal_position";
                break;
            case POSTGRESQL:
                sql = "select a.\"COLUMN_NAME\",b.\"COLUMN_COMMENT\",a.\"DATA_TYPE\",a.\"DATA_LENGTH\",a.\"DATA_SCALE\" from \n" +
                        "(select column_name \"COLUMN_NAME\",udt_name \"DATA_TYPE\",COALESCE(numeric_precision,character_maximum_length) \"DATA_LENGTH\" ,numeric_scale \"DATA_SCALE\",table_name from information_schema.columns where table_schema='public' order by ordinal_position) a,(SELECT c.attname \"COLUMN_NAME\",col_description(C.attrelid,C.attnum) \"COLUMN_COMMENT\",RELNAME table_name FROM PG_CLASS B ,pg_attribute c WHERE  c.ATTRELID= b.OID and  attnum>0) b where a.\"COLUMN_NAME\" = b.\"COLUMN_NAME\"  and a.table_name =b.table_name and a.table_name = ? ";
                break;
            default:
                DeanRuntimeException.throwException(1001,"暂不支持的数据库类型") ;
        }
        return sql;
    }

    public static String createInsertSql(String tableName,String [] fields){
        return createInsertSql( tableName, fields, false);
    }

    /**
     * 创建 数据插入语句
     * @param tableName
     * @param fields
     * @return
     */
    public static String createInsertSql(String tableName,String [] fields,boolean remarkFlag) {
        // 判断表头是否含有remark字段
        StringBuilder sql = new StringBuilder("insert into ");
        sql.append(tableName);
        sql.append("(");
        StringBuilder cols = new StringBuilder();
        StringBuilder vals = new StringBuilder();
        for (int i = 0; i < fields.length; i++) {
            cols.append(fields[i]);
            vals.append("?");
            if(i+1 < fields.length){
                cols.append(",");
                vals.append(",");
            }
            if(DeanUiConstants.REMARK.equals(fields[i].toUpperCase())){
                remarkFlag = true;
            }
        }
        if(remarkFlag){
            cols.append(",remark");
            vals.append(",?");
        }
        sql.append(cols);
        sql.append(") values (");
        sql.append(vals);
        sql.append(")");
        return sql.toString();
    }

    public static String buildInsertSql(String tableName,String [] fields){
        return buildInsertSql(tableName,fields,null);
    }


    public static String buildInsertSql(String tableName,String [] fields,String fileName){
        return buildInsertSql(tableName,fields,fileName,null);
    }

    /**
     * 创建 数据插入语句
     * @param tableName
     * @param fields
     * @return
     */
    public static String buildInsertSql(String tableName,String [] fields,String fileName,String sheetName) {
        // step 1 构建默认参数
        Map<String,String> params = new HashMap<String,String>();
        params.put("DATA_DATE", LocalDate.now().format(DateTimeFormatter.ofPattern("yyyyMMdd")));
        params.put("FILE_NAME",fileName.substring(0,fileName.lastIndexOf(".")));
        params.put("FILE_EXTENSION",fileName.substring(fileName.lastIndexOf(".")+1));
        params.put("SHEET_NAME",sheetName==null?"":sheetName);
        logger.debug("build sql params is {}",params);

        // 判断表头是否含有remark字段
        StringBuilder sql = new StringBuilder("insert into ");
        sql.append(tableName);
        sql.append("(");
        StringBuilder cols = new StringBuilder();
        StringBuilder vals = new StringBuilder();
        for (int i = 0; i < fields.length; i++) {
            String colName = fields[i];
            // 按照=号截取
           String [] conditions =  colName.split("=");
           if(conditions.length == 1){
               vals.append("?");
           }else{
               String val = conditions[1];
               vals.append("'"+parse(val,params)+"'");
           }
            cols.append(conditions[0]);
            if(i+1 < fields.length){
                cols.append(",");
                vals.append(",");
            }
        }
        sql.append(cols);
        sql.append(") values (");
        sql.append(vals);
        sql.append(")");
        logger.info("insert sql build successfully![{}]",sql.toString());
        return sql.toString();
    }

//    private static String parse(String val,Map<String,String> params){
//        int begin = val.indexOf("${");
//        int end = val.indexOf("}");
//        while( begin > -1 &&  end > -1){
//            String key = val.substring(begin+2,end-1);
//            val = val.replace("${"+key+"}",params.get(key));
//            begin = val.indexOf("${",end+1);
//            end = val.indexOf("}",end+1);
//        }
//        return val;
//    }

    /**
     * 解析表达式
     * @param val
     * @param params
     * @return
     */
    private static String parse(String val, Map<String,String> params){
        // 含有表达式需要解析
        int begin = val.indexOf("${");
        int end = val.indexOf("}");
        while( begin > -1 &&  end > -1){
            String key = val.substring(begin+2,end);
            if(key.toLowerCase().contains(DEAN_SPLIT_SUBSTR)){
                String newKey = key.substring(DEAN_SPLIT_SUBSTR.length()+1,key.length()-1);
                String [] funcs = newKey.split(",");
                if(funcs.length==2 || funcs.length==3){
                    int index = 0;
                    if(funcs.length==3 ){
                        index = Integer.parseInt(funcs[2]);
                    }
                    val=val.replace("${"+key+"}",splitSubstring(params.get(funcs[0]),funcs[1],index));
                }else{
                    throw DeanRuntimeException.throwException(996,"dean_split_substr function need 2 or 3 params but get "+funcs.length+" params");
                }
            }else if(key.toLowerCase().contains(DEAN_SUBSTR)){
                String newKey = key.substring(DEAN_SUBSTR.length()+1,key.length()-1);
                String [] funcs = newKey.split(",");
                if(funcs.length==2 || funcs.length==3){
                    int fromIndex = 0;
                    int subLen = 0;
                    if(funcs.length == 2){
                        subLen = Integer.parseInt(funcs[2])-1;
                    }else{
                        fromIndex = Integer.parseInt(funcs[1])-1;
                        subLen = Integer.parseInt(funcs[2]);
                    }

                    val=val.replace("${"+key+"}",params.get(funcs[0]).substring(fromIndex,fromIndex+subLen));
                }else{
                    throw DeanRuntimeException.throwException(996,"dean_substr function need 2 or 3 params but get "+funcs.length+" params");
                }

            }
            else{
                val = val.replace("${"+key+"}",params.get(key));
            }
            begin = val.indexOf("${",end+1);
            end = val.indexOf("}",end+1);
        }
        return val;
    }

    public static String createPageSql(String dbType,String sql){
        String exeSql = "select * from ("+sql+") t ";
        switch (dbType){
            case ORALCE:
                exeSql = exeSql + " where rownum<= ?";
                break;
            case MYSQL:
                exeSql = exeSql + " limit ?";
                break;
            case POSTGRESQL:
                exeSql = exeSql + " limit ?";
                break;
            default:
                DeanRuntimeException.throwException(1001,"暂不支持的数据库类型") ;
        }
        return exeSql;
    }

    /**
     * 分割截取
     * @param content
     * @param split
     * @param index
     * @return
     */
    private static String splitSubstring(String content,String split,Integer index){
        String [] strs = content.split(split);
        int idx = index==null?0:index;
        return strs[idx];
    }
}
